﻿// <copyright file="CategoryContext.cs" company="Auros Ltd.">
//     Copyright (c) Auros Ltd. All rights reserved.
// </copyright>

namespace Auros.Categorisation.Data
{
    using System;
    using System.Data;
    using umbraco.DataLayer;

    /// <summary>
    /// Class representing a category data context.
    /// </summary>
    internal class CategoryDAL
    {
        private const string SelectCommand = "SELECT *, CASE WHEN EXISTS (SELECT id FROM Categories Temp WHERE Temp.parentId = Categories.id) THEN 1 ELSE 0 END AS hasChildren FROM Categories";
        private const string SelectSingleCommand = "SELECT *, CASE WHEN EXISTS (SELECT id FROM Categories Temp WHERE Temp.parentId = Categories.id) THEN 1 ELSE 0 END AS hasChildren FROM Categories WHERE id = @id";
        private const string SelectChildrenCommand = "SELECT *, CASE WHEN EXISTS (SELECT id FROM Categories Temp WHERE Temp.parentId = Categories.id) THEN 1 ELSE 0 END AS hasChildren FROM Categories WHERE parentId = @id";
        private const string InsertCommand = "INSERT INTO Categories ([name], [description], [parentId], [selectable], [enabled], [sortOrder]) VALUES (@name, @desctext, @parentId, @selectable, @enabled, @sortOrder);";
        private const string UpdateCommand = "UPDATE Categories SET [name] = @name, [description] = @desctext, [parentId] = @parentId, [selectable] = @selectable, [enabled] = @enabled, [sortOrder] = @sortOrder WHERE [id] = @id";
        private const string DeleteCommand = "DELETE FROM Categories WHERE [id] = @id";

        private string connectionString;

        internal CategoryDAL(string connectionString)
        {
            this.connectionString = connectionString;
        }

        internal DataTable GetAll()
        {
            DataTable table = new DataTable();

            table.Columns.Add("id", typeof(int));
            table.Columns.Add("name", typeof(string));
            table.Columns.Add("description", typeof(string));
            table.Columns.Add("parentId", typeof(int));
            table.Columns.Add("sortOrder", typeof(int));
            table.Columns.Add("selectable", typeof(bool));
            table.Columns.Add("enabled", typeof(bool));
            table.Columns.Add("hasChildren", typeof(int));

            using (var reader = umbraco.BusinessLogic.Application.SqlHelper.ExecuteReader(SelectCommand))
            {
                while (reader.Read())
                {
                    var row = table.NewRow();

                    foreach (DataColumn column in table.Columns)
                    {
                        row[column.ColumnName] = reader.GetObject(column.ColumnName);
                    }

                    table.Rows.Add(row);
                }
            }

            return table;
        }

        internal DataRow GetSingle(int id)
        {
            DataTable table = new DataTable();

            table.Columns.Add("id", typeof(int));
            table.Columns.Add("name", typeof(string));
            table.Columns.Add("description", typeof(string));
            table.Columns.Add("parentId", typeof(int));
            table.Columns.Add("sortOrder", typeof(int));
            table.Columns.Add("selectable", typeof(bool));
            table.Columns.Add("enabled", typeof(bool));
            table.Columns.Add("hasChildren", typeof(int));

            using(var reader = umbraco.BusinessLogic.Application.SqlHelper.ExecuteReader(SelectSingleCommand,
                umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@id", id)
            ))
            {
                while(reader.Read())
                {
                    var row = table.NewRow();

                    foreach (DataColumn column in table.Columns)
	                {
		                row[column.ColumnName] = reader.GetObject(column.ColumnName);
	                }

                    table.Rows.Add(row);
                }
            }

            return table.Rows.Count > 0 ? table.Rows[0] : null;
        }

        internal DataTable GetItemsByParentId(int parentId)
        {
            DataTable table = new DataTable();

            table.Columns.Add("id", typeof(int));
            table.Columns.Add("name", typeof(string));
            table.Columns.Add("description", typeof(string));
            table.Columns.Add("parentId", typeof(int));
            table.Columns.Add("sortOrder", typeof(int));
            table.Columns.Add("selectable", typeof(bool));
            table.Columns.Add("enabled", typeof(bool));
            table.Columns.Add("hasChildren", typeof(int));

            using (var reader = umbraco.BusinessLogic.Application.SqlHelper.ExecuteReader(SelectChildrenCommand,
                umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@id", parentId)
            ))
            {
                while (reader.Read())
                {
                    var row = table.NewRow();

                    foreach (DataColumn column in table.Columns)
                    {
                        row[column.ColumnName] = reader.GetObject(column.ColumnName);
                    }

                    table.Rows.Add(row);
                }
            }

            return table;
        }

        internal Category Create(Category category)
        {
            int newId = this.Create(category.Name, category.Description, category.Selectable, category.Enabled, category.ParentID.GetValueOrDefault(0), category.SortOrder);

            category.ID = newId;

            return category;
        }

        internal int Create(string name, string description, bool selectable, bool enabled, int parentId, int sortOrder)
        {
            int newId = -1;

            umbraco.BusinessLogic.Application.SqlHelper.ExecuteNonQuery(InsertCommand,
                umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@name", name ?? string.Empty),
                umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@desctext", description ?? string.Empty),
                umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@selectable", selectable),
                umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@enabled", enabled),
                umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@parentId", parentId),
                umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@sortOrder", sortOrder)
            );

            newId = umbraco.BusinessLogic.Application.SqlHelper.ExecuteScalar<int>("SELECT MAX(id) FROM Categories");

            return newId;
        }

        internal void Update(int id, string name, string description, bool selectable, bool enabled, int parentId, int sortOrder)
        {
            umbraco.BusinessLogic.Application.SqlHelper.ExecuteNonQuery(UpdateCommand,
                umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@id", id),
                umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@name", name ?? string.Empty),
                umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@desctext", description ?? string.Empty),
                umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@selectable", selectable),
                umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@enabled", enabled),
                umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@parentId", parentId),
                umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@sortOrder", sortOrder)
            );
        }

        internal void Delete(int id)
        {
            umbraco.BusinessLogic.Application.SqlHelper.ExecuteNonQuery(DeleteCommand,
                umbraco.BusinessLogic.Application.SqlHelper.CreateParameter("@id", id)
            );
        }
    }
}